/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package formularios;

import java.awt.Rectangle;
import java.sql.ResultSet;  
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.Hashtable;
import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import Clases.Tools;
import Clases.Transacciones;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Date;

/**
 *
 * @author Samuel
 */
public class CorteCaja extends javax.swing.JInternalFrame {
    public static int IdMenu=8;
    private DefaultTableModel modelo;
    public JTable tabla;
    double _TotalCheque = 0;
    double _TotalEfectivo =0;
    double _TotalT  = 0;
    String _idmovs ="0";
    /**
     * Creates new form CorteCaja
     */
    public CorteCaja() {
        initComponents();
//        {"idmov","No. Recibo","Fecha","Cliente","IdFormaPago","Forma de Pago","Usuario","Articulo","cantidad","Precio","Total"};
        final Class<?>[] columnClasses = {Integer.class,String.class, Date.class,String.class, Integer.class,String.class,String.class,String.class,  Double.class,Double.class,Double.class}; 
        modelo = new DefaultTableModel()
        {
            @Override
                public Class<?> getColumnClass(int columnIndex)
                {
                    return columnClasses[columnIndex];
                }
        };
        tabla = new JTable();
        modeloTabla ();
        llenarEncabezadoOrden();
        
    }
    
    private void guardar ()
    {        
        Connection _conexion=null;
        try
        {         
            int idcorte =0;
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            _conexion = DriverManager.getConnection("jdbc:mysql://"+MainMenu.server+"/"+MainMenu.dataBase,MainMenu.user, MainMenu.password);
            _conexion.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            _conexion.setAutoCommit(false);//Inicia la transaccion
            Statement st = _conexion.createStatement();
            String strComando =("insert into cortes (fecha, idusuario, TotalEfectivo, TotalCheques) "
                    + "values  (now(),"+MainMenu.IdUsuario+", '"+ _TotalEfectivo +"','"+ _TotalCheque+"' )");  
            st.execute( strComando,Statement.RETURN_GENERATED_KEYS);
            ResultSet rs = st.getGeneratedKeys();
            if(rs.next()) 
            {
                idcorte = rs.getInt(1);               
            }
            String sql = ("update movinventario set idcorte = '"+ idcorte +"'  where idmov in ("+  _idmovs + " ) ");
            st.execute(sql);
            //Creacion de partida
            int idmovcuenta=0;
            int bancos = 5;
            int caja = 4;
            int ingresosPorAplicar = 41;
            int ingresos = 21;

            String query="insert into movcuenta (fecha, tipo, id, observaciones, idusuario) "
              + " values (now(),'movinventario',3,'movinventario',"+MainMenu.IdUsuario+")" ;
            st.execute(query,Statement.RETURN_GENERATED_KEYS);
            ResultSet rs1 = st.getGeneratedKeys();
            
            if(rs1.next()) 
            {
                idmovcuenta  = rs1.getInt(1);               
            }
            
            //Generando banco a caja
            sql = ("insert into detallemovcuenta (idmovcuenta,idcuenta,debe,haber) "
                     + "values ('"+idmovcuenta+"',"+bancos+" ,'"+_TotalEfectivo +"',0)");
            st.execute(sql);
                
            sql = ("insert into detallemovcuenta (idmovcuenta,idcuenta,debe,haber) "
                    + "values ('"+idmovcuenta+"',"+caja+" ,0,'"+_TotalEfectivo +"')");
            st.execute(sql);
            //Generando patrimonio a ingresos
            sql = ("insert into detallemovcuenta (idmovcuenta,idcuenta,debe,haber) "
                     + "values ('"+idmovcuenta+"',"+ingresosPorAplicar+" ,'"+_TotalEfectivo +"',0)");
            st.execute(sql);
               
            sql = ("insert into detallemovcuenta (idmovcuenta,idcuenta,debe,haber) "
                    + "values ('"+idmovcuenta+"',"+ingresos+" ,0,'"+_TotalEfectivo +"')");
            st.execute(sql);
            //Fin de partidas
            
            //Actualizacion de Saldos
            
            //Suma a bancos
            sql = ("update cuentas set saldo = saldo + "+_TotalT +" where idcuenta in ("+bancos+") ");
            st.execute(sql);
            //Resta a caja
            sql = ("update cuentas set saldo = saldo - "+_TotalT +" where idcuenta in ("+caja+") ");
            st.execute(sql); 
            //suma ingresos
            sql = ("update cuentas set saldo = saldo + "+_TotalT +" where idcuenta in ("+ingresos+") ");
            st.execute(sql);; 
            //resta ingresos por aplicar
            sql = ("update cuentas set saldo = saldo - "+_TotalT +" where idcuenta in ("+ingresosPorAplicar+") ");
            st.execute(sql);
            //
            _conexion.commit();
            JOptionPane.showMessageDialog(this, "Los datos se almacenaron correctamente","Información",JOptionPane.INFORMATION_MESSAGE);
        }         
        catch(Exception ex)
        {
            try {
                _conexion.rollback();
            } catch (SQLException ex1) {
                 
            }
        }  
    }
    
//    private void crearPartida()
//    {
//        int idmovcuenta=0;
//        int bancos = 5;
//        int caja = 4;
//        int patrimonio = 20;
//        int ingresos = 21;
//        try
//        {           
//             String strComando="insert into movcuenta (fecha, tipo, id, observaciones, idusuario) "
//                + " values (now(),'movinventario',3,'movinventario',"+MainMenu.IdUsuario+")" ;;
//            String resultado=MainMenu.conexion.Ejecutar(strComando);
//            ResultSet rs = MainMenu.conexion.EjecutarConsulta("select max(idmovcuenta) from movcuenta");
//             if(rs.next()) 
//            {
//                idmovcuenta  = rs.getInt(1);               
//            }
//            //Generando banco a caja
//             String sql = ("insert into detallemovcuenta (idmovcuenta,idcuenta,debe,haber) "
//              + "values ('"+idmovcuenta+"',"+bancos+" ,'"+_TotalEfectivo +"',0)");
//                MainMenu.conexion.Ejecutar(sql);
//                
//            sql = ("insert into detallemovcuenta (idmovcuenta,idcuenta,debe,haber) "
//              + "values ('"+idmovcuenta+"',"+caja+" ,0,'"+_TotalEfectivo +"')");
//            MainMenu.conexion.Ejecutar(sql);
//            //Generando patrimonio a ingresos
//            sql = ("insert into detallemovcuenta (idmovcuenta,idcuenta,debe,haber) "
//              + "values ('"+idmovcuenta+"',"+patrimonio+" ,'"+_TotalEfectivo +"',0)");
//                MainMenu.conexion.Ejecutar(sql);
//                
//            sql = ("insert into detallemovcuenta (idmovcuenta,idcuenta,debe,haber) "
//              + "values ('"+idmovcuenta+"',"+ingresos+" ,0,'"+_TotalEfectivo +"')");
//            MainMenu.conexion.Ejecutar(sql);
//            
//            actualizarSaldos();
//        }        
//        catch(Exception ex)
//        {
//             JOptionPane.showMessageDialog(null, ex.getMessage(), "Error al intentar conectar a la base de datos", JOptionPane.ERROR_MESSAGE);
//        } 
//        
//    }
    
//    private void actualizarSaldos()
//    {
//        int bancos =  5;
//        int caja =  4;
//        int patrimonio = 20;
//        int ingresos =21;
//        //Suma a bancos
//       String sql = ("update cuentas set saldo = saldo + "+_TotalT +" where idcuenta in ("+bancos+") ");
//       MainMenu.conexion.Ejecutar(sql); 
//       //Resta a caja
//       sql = ("update cuentas set saldo = saldo - "+_TotalT +" where idcuenta in ("+caja+") ");
//       MainMenu.conexion.Ejecutar(sql); 
//       //suma Patrimonio
//       sql = ("update cuentas set saldo = saldo + "+_TotalT +" where idcuenta in ("+ingresos+") ");
//       MainMenu.conexion.Ejecutar(sql); 
//       //resta patrimonio
//       sql = ("update cuentas set saldo = saldo - "+_TotalT +" where idcuenta in ("+patrimonio+") ");
//       MainMenu.conexion.Ejecutar(sql); 
//    }
    
    
    private void llenarEncabezadoOrden()
    {
      limpiarTabla();   
      double subtotal =0, total =0;
      double cantidad=0, precio =0;
      String comparar = "";
      int idformapago =0;
       ResultSet rs = MainMenu.conexion.EjecutarConsulta("select m.idmov, m.norecibo, m.fecha, e.nombre, f.idformapago,  f.nombre, "
                + " u.nombre, a.nombre, d.cantidad, d.precio, Sum(d.cantidad * d.precio) total "
                + " from movinventario m "
                + " inner join detallemovinventario d on m.idmov = d.idmov "
                + " inner join entidades e on m.identidad = e.identidad "
                + " inner join formapago f on m.idformapago = f.idformapago "
                + " inner join tipomov t on  m.idtipomov = t.idtipomov "
                + " inner join usuarios u on m.idusuario = u.idusuario "
                + " inner join articulos a on d.idarticulo = a.idarticulo "
                + " where d.debaja is null and m.debaja is null  "
                + " and m.idcorte is null and m.idtipomov =  1 group by m.idmov");        
       //and m.fecha =  '"+Tools.ObtenerFechaSql(jDateFecha.getDate())+"' 
       int agregarfila = 0;
        try 
        {
            while(rs.next())
            { 
                DefaultTableModel temp3 = (DefaultTableModel) jTdetalle.getModel();
                Object nuevo1[]= {temp3.getRowCount()+1,"",""};
                temp3.addRow(nuevo1);
                jTdetalle.setValueAt(rs.getInt(1),agregarfila,0); //idmov   
                jTdetalle.setValueAt(rs.getString(2).toString(),agregarfila,1); //NoRecibo
                jTdetalle.setValueAt(rs.getDate(3),agregarfila,2); //fecha
                jTdetalle.setValueAt(rs.getString(4).toString(),agregarfila,3); //entidad nombre
                jTdetalle.setValueAt(rs.getInt(5),agregarfila,4); //idforma pago
                jTdetalle.setValueAt(rs.getString(6).toString(),agregarfila,5); //forma pago
                jTdetalle.setValueAt(rs.getString(7).toString(),agregarfila,6); //usuario
                jTdetalle.setValueAt(rs.getString(8).toString(),agregarfila,7); //nombre articulo
                jTdetalle.setValueAt(rs.getDouble(9),agregarfila,8); //cantidad
                jTdetalle.setValueAt(rs.getDouble(10),agregarfila,9); //precio
                jTdetalle.setValueAt(rs.getDouble(11),agregarfila,10); //subtotal
                
                idformapago = rs.getInt(5);
                subtotal = rs.getDouble(11);
                if (!rs.getString(1).equals(comparar))
                    _idmovs =  _idmovs + ", "+ rs.getString(1);
                comparar = rs.getString(1).toString();
                if (idformapago == 2)
                {
                    _TotalCheque = _TotalCheque + subtotal;
                }
                if (idformapago == 1)
                {
                    _TotalEfectivo = _TotalEfectivo + subtotal;
                }
                 
                agregarfila++;
            }  
            jTTotalEfectivo.setText(String.valueOf(_TotalEfectivo));
            jTTotalCheque.setText(String.valueOf(_TotalCheque));
            _TotalT = _TotalEfectivo + _TotalCheque;
            tfTotal.setText(String.valueOf(_TotalT));
        }
        catch(Exception ex)
        {
            JOptionPane.showMessageDialog(null, ex.getMessage(), "Error al intentar conectar a la base de datos", JOptionPane.ERROR_MESSAGE);
        }
       
    }
    private void modeloTabla ()             
    {
        String[] columnNames = {"idmov","No. Recibo","Fecha","Cliente","IdFormaPago","Forma de Pago","Usuario","Articulo","cantidad","Precio","Total"};
        int[] OcultarColumas = {1};
        modelo.setColumnIdentifiers(columnNames);              
        modelo.setColumnCount(11);        
        modelo.setRowCount(0);               
        tabla.setModel(modelo);        
        tabla.setBounds(new Rectangle(10, 60, 510, 370));        
        jTdetalle.setModel(modelo);   
         //oculta las columnas 0, 1 , 2 , 3
        Hashtable ht = new Hashtable();
        ht.clear();  
        ht.put(0, 0);
        ht.put(1, 4);
        ht.put(2,7);
        ht.put(3, 8);
        ht.put(4, 9);
        ocultarColumnas (ht,jTdetalle);
    }
    
    private void ocultarColumnas(Hashtable no, JTable t)
    { 
        Enumeration e = no.elements();
        Object valor;
        while( e.hasMoreElements() )
        {
            valor = e.nextElement();
            int a = Integer.parseInt(valor.toString());
            t.getColumnModel().getColumn(a).setMinWidth(0);
            t.getColumnModel().getColumn(a).setMaxWidth(0);
            t.getColumnModel().getColumn(a).setWidth(0);
            t.getColumnModel().getColumn(a).setWidth(0);
        }
    }
    
    public void limpiarTabla()
    {
        int a =modelo.getRowCount()-1;
        for(int i=a; i>=0;i--)
        {        
            modelo.removeRow(i );
        }
    }
     


    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jScrollPane1 = new javax.swing.JScrollPane();
        jTdetalle = new javax.swing.JTable();
        jPanel5 = new javax.swing.JPanel();
        jLabel9 = new javax.swing.JLabel();
        jLabel12 = new javax.swing.JLabel();
        jLabel10 = new javax.swing.JLabel();
        jTTotalEfectivo = new javax.swing.JTextField();
        jTTotalCheque = new javax.swing.JTextField();
        tfTotal = new javax.swing.JTextField();
        btnGuardar = new javax.swing.JButton();

        setClosable(true);
        setMaximizable(true);
        setResizable(true);
        setTitle("Corte de Caja");

        jTdetalle.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null}
            },
            new String [] {
                "Title 1", "Title 2", "Title 3", "Title 4"
            }
        ));
        jScrollPane1.setViewportView(jTdetalle);

        jPanel5.setBorder(javax.swing.BorderFactory.createEtchedBorder());

        jLabel9.setFont(new java.awt.Font("Tahoma", 0, 14)); // NOI18N
        jLabel9.setText("Total Efecivo");

        jLabel12.setFont(new java.awt.Font("Tahoma", 0, 14)); // NOI18N
        jLabel12.setText("Total Cheques");

        jLabel10.setFont(new java.awt.Font("Tahoma", 0, 18)); // NOI18N
        jLabel10.setText("Total");

        jTTotalEfectivo.setEditable(false);
        jTTotalEfectivo.setHorizontalAlignment(javax.swing.JTextField.RIGHT);

        jTTotalCheque.setEditable(false);
        jTTotalCheque.setHorizontalAlignment(javax.swing.JTextField.RIGHT);

        tfTotal.setEditable(false);
        tfTotal.setFont(new java.awt.Font("Tahoma", 0, 18)); // NOI18N
        tfTotal.setHorizontalAlignment(javax.swing.JTextField.RIGHT);

        btnGuardar.setIcon(new javax.swing.ImageIcon(getClass().getResource("/imagenes/floppy24.png"))); // NOI18N
        btnGuardar.setText("Guardar");
        btnGuardar.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnGuardarActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout jPanel5Layout = new javax.swing.GroupLayout(jPanel5);
        jPanel5.setLayout(jPanel5Layout);
        jPanel5Layout.setHorizontalGroup(
            jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel5Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jLabel9)
                    .addComponent(jLabel12)
                    .addComponent(jLabel10))
                .addGap(36, 36, 36)
                .addGroup(jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(tfTotal, javax.swing.GroupLayout.PREFERRED_SIZE, 164, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addGroup(jPanel5Layout.createSequentialGroup()
                        .addGroup(jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                            .addComponent(jTTotalEfectivo, javax.swing.GroupLayout.DEFAULT_SIZE, 165, Short.MAX_VALUE)
                            .addComponent(jTTotalCheque))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                        .addComponent(btnGuardar)))
                .addContainerGap())
        );
        jPanel5Layout.setVerticalGroup(
            jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel5Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel9)
                    .addComponent(jTTotalEfectivo, javax.swing.GroupLayout.PREFERRED_SIZE, 28, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(btnGuardar))
                .addGap(16, 16, 16)
                .addGroup(jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jLabel12)
                    .addComponent(jTTotalCheque, javax.swing.GroupLayout.PREFERRED_SIZE, 28, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGroup(jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(jPanel5Layout.createSequentialGroup()
                        .addGap(17, 17, 17)
                        .addComponent(jLabel10))
                    .addGroup(jPanel5Layout.createSequentialGroup()
                        .addGap(18, 18, 18)
                        .addComponent(tfTotal, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addContainerGap()
                        .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 947, Short.MAX_VALUE))
                    .addGroup(layout.createSequentialGroup()
                        .addContainerGap()
                        .addComponent(jPanel5, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)))
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 277, Short.MAX_VALUE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jPanel5, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap())
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void btnGuardarActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnGuardarActionPerformed
        int answer=JOptionPane.showConfirmDialog(null, "¿Desea hacer el corte de caja?", "Confirme por favor", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE);
        if(answer == JOptionPane.YES_OPTION)
        {
            guardar ();
            limpiarTabla();
            jTTotalEfectivo.setText("0.00");
            jTTotalCheque.setText("0.00");
            tfTotal.setText("");
        }
        int respuesta=JOptionPane.showConfirmDialog(null, "¿Desea imprimir el corte?", "Confirme por favor", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE);
        if(respuesta == JOptionPane.YES_OPTION)
        {
            rptCortedeCaja rep = new rptCortedeCaja(null, true);
            rep.setVisible(true);
        }
    }//GEN-LAST:event_btnGuardarActionPerformed

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton btnGuardar;
    private javax.swing.JLabel jLabel10;
    private javax.swing.JLabel jLabel12;
    private javax.swing.JLabel jLabel9;
    private javax.swing.JPanel jPanel5;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JTextField jTTotalCheque;
    private javax.swing.JTextField jTTotalEfectivo;
    private javax.swing.JTable jTdetalle;
    private javax.swing.JTextField tfTotal;
    // End of variables declaration//GEN-END:variables
}
